﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_GetContentByCode')
BEGIN
    PRINT 'Dropping Procedure proc_cm_GetContentByCode'
    DROP  Procedure  proc_cm_GetContentByCode
END
GO

PRINT 'Creating Procedure proc_cm_GetContentByCode'
GO

CREATE Procedure dbo.proc_cm_GetContentByCode
	@pHtmlPageId uniqueidentifier
	, @pContentCd varchar(50)
	, @pCreateIfNotExists bit = 1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF @pCreateIfNotExists = 1 AND NOT EXISTS ( SELECT * FROM [dbo].[tbl_v2_Content] WHERE content_cd = @pContentCd )
	BEGIN
		declare @vPriorityNum int

		if exists (select * from [dbo].[tbl_v2_Content] where [html_page_id] = @pHtmlPageId and [priority_num] = 0)
		begin
			update [dbo].[tbl_v2_Content]
			set [priority_num] = [priority_num] + 1
			where [html_page_id] = @pHtmlPageId
		end
				
		INSERT INTO [dbo].[tbl_v2_Content]
		(
			[content_cd], 
			[content_txt], 
			[content_html_txt], 
			[create_dttm], 
			[update_dttm],
			[html_page_id],
			[display_sectionheader_ind],
			[sectionheader_txt],
			[divider_color_txt],
			[priority_num]
		)
		SELECT	@pContentCd,
			'',
			'',
			GETDATE(),
			GETDATE(),
			@pHtmlPageId,
			0,
			null,
			null,
			0
	END

	select
		[content_id]
		,[content_cd]
		,[content_txt]
		,[create_dttm]
		,[update_dttm]
		,[content_html_txt]
		,[html_page_id]
		,[display_sectionheader_ind]
		,[sectionheader_txt]
		,[divider_color_txt]
		,[priority_num]
	from
		[dbo].[tbl_v2_Content]
	where
		content_cd = @pContentCd 
		and html_page_id = @pHtmlPageId
END
GO

GRANT EXEC ON dbo.proc_cm_GetContentByCode TO PUBLIC
GO

